''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：alibaba_product.py
#日期：2023-7-15
#备注：阿里巴巴国际站产品列表
1. 产品分组
2. 产品列表
CREATE TABLE `alibaba_product_group` (
  `groupId` int(11) COMMENT '产品分组id', 
  `groupName` varchar(50) NOT NULL COMMENT '产品分组名称',
  `groupLevel` int(5) COMMENT '分组级别',  # 1,2,3
  `parentGroupId` int(11) COMMENT '父分组id',  # -1是第一级，其他是分组产品id
  `hasChildren` varchar(20) NOT NULL COMMENT '是否有子分组',
  `childrenCount` int(5) COMMENT '子产品分组数量', 
  `productCount` int(5) COMMENT '分组下的产品数量', 
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`groupId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='阿里巴巴产品分组信息';

CREATE TABLE `alibaba_product` (
  `id` varchar(20) COMMENT '产品id', 
  `groupName1` varchar(50) COMMENT '产品分组1',
  `groupName2` varchar(50) COMMENT '产品分组2',
  `groupRemark` varchar(50) COMMENT '产品说明：9D,21D',
  `showNum` int(10) COMMENT '月曝光量', 
  `clickNum` int(10) COMMENT '月点击量', 
  `qualityScore` float COMMENT '质量分', 
  `tradeType` varchar(20) COMMENT '是否是RTS产品',  # notRts，rts
  `subject` varchar(150) NOT NULL COMMENT '产品标题',
  `keywords` varchar(200) NOT NULL COMMENT '产品关键词',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='阿里巴巴产品信息';

CREATE TABLE `alibaba_product_hq` (
  `productId` varchar(20) COMMENT '产品id', 
  `imageUrl` varchar(200) COMMENT '产品图片',
  `subject` varchar(200) COMMENT '产品标题',
  `tradeType` varchar(20) COMMENT '产品分类：定制/RST',
  `tag` varchar(50) COMMENT '产品标签：是否橱窗、推广',  
  `searchImpsCnt30d` int(10) COMMENT '近30天搜索曝光', 
  `detailUv30d` int(10) COMMENT '近30天访问人数', 
  `fbTmUv90d` int(10) COMMENT '近90天[TM+询盘]人数',
  `fbTmUv90dRate` float COMMENT '近90天[TM+询盘]转化%',  
  `byrCnt90d` int(10) COMMENT '近30天访问人数', 
  `publishDays` int(10) COMMENT '产品发布天数', 
  `create_date` date COMMENT '创建日期',
  PRIMARY KEY (`productId`,`create_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='阿里巴巴实力优品信息';
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

from pycacho.cachobase.file_deal import replace_txt
from pacong.stock.stock_config import db,cursor
from pycacho.cachobase.logger import Logger
from pycacho.cachobase.deal_time import minus_date
from pycacho.cachobase.deal_list import list_to_str
import os
logger = Logger("alibaba_product").get_log()
today = minus_date(0,'t')

def database():
    sql1 = 'delete from alibaba_product_group;'
    sql2 = 'delete from alibaba_product;'
    cursor.execute(sql2)

# 橱窗产品
def get_product_ad():
    file = 'F:\\alibaba\json\\productAd.txt'
    ad_list = replace_txt(file)
    ad_products= []
    for product in ad_list:
        productId = product['productId']
        imageUrl = product['imageUrl'].replace('_100x100.jpg', '').replace('_100x100.png', '')  # 产品图片
        subject = product['subject']  # 标题
        ad_products.append(productId)
        print(productId, subject,imageUrl)
    return ad_products

# 实力优品
def get_product_hq():
    file = 'F:\\alibaba\json\\productHighQuality.json'
    hq_list = replace_txt(file)
    for product in hq_list:
        productId = product['productId']
        imageUrl = product['absSummImageUrl'].replace('_100x100.jpg', '').replace('_100x100.png', '')  # 产品图片
        subject = product['subject']  # 标题
        tradeType = product['tradeType']  # 定制/RST
        tag = list_to_str(product['tagList'])  # 是否橱窗产品
        searchImpsCnt30d = product['searchImpsCnt30d']  # 近30天搜索曝光
        detailUv30d = product['detailUv30d']  # 近30天访问人数
        fbTmUv90d = product['fbTmUv90d']  # 近90天[TM+询盘]人数
        fbTmUv90dRate = product['fbTmUv90dRate']*100  # 近90天[TM+询盘]转化%
        byrCnt90d = product['byrCnt90d']  # 近90天支付买家数
        publishDays = product['publishDays']  # 发布天数
        print(productId,imageUrl,subject,tradeType,tag,searchImpsCnt30d,detailUv30d,fbTmUv90d,fbTmUv90dRate,byrCnt90d,publishDays,today)
        insert_product_hq(productId,imageUrl,subject,tradeType,tag,searchImpsCnt30d,detailUv30d,fbTmUv90d,fbTmUv90dRate,byrCnt90d,publishDays,today)

def get_product_group():
    file = 'F:\\alibaba\json\\group2.txt'
    file_name = os.path.basename(file)
    group_list = replace_txt(file)
    for group in group_list:
        groupId =  group['id']
        groupName = group['name']
        hasChildren = group['hasChildren']
        childrenCount = group['childrenCount']
        productCount = group['productCount']
        if file_name=='group2.txt':
            groupLevel = 2
            parentGroupId = 929462875
        else:
            groupLevel = 1
            parentGroupId = -1
        insert_product_group(groupId,groupName,hasChildren,childrenCount,productCount,groupLevel,parentGroupId)

def get_product_rank():
    file = 'F:\\alibaba\json\\productRank.txt'
    rank_list = replace_txt(file)
    for group in rank_list:
        groupId =  group['id']
        prodName = group['prodName']
        sumProdShowNum = group['sumProdShowNum']  # 搜索曝光
        sumProdClickNum = group['sumProdClickNum'] # 搜索点击
        if groupId not in get_product_ad():
            print(groupId,sumProdShowNum,sumProdClickNum,prodName)

def get_product_count():
    file = 'F:\\alibaba\json\\groupcount2.txt'
    group_count = replace_txt(file)
    for key in group_count:
        update_product(group_count[key],key)


# 将阿里巴巴实力优品信息存入到数据库
def insert_product_hq(productId,imageUrl,subject,tradeType,tag,searchImpsCnt30d,detailUv30d,fbTmUv90d,fbTmUv90dRate,byrCnt90d,publishDays,today):
    sql = "insert into alibaba_product_hq(productId,imageUrl,subject,tradeType,tag,searchImpsCnt30d,detailUv30d,fbTmUv90d,fbTmUv90dRate,byrCnt90d,publishDays,create_date) " \
            "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
            % (productId,imageUrl,subject,tradeType,tag,searchImpsCnt30d,detailUv30d,fbTmUv90d,fbTmUv90dRate,byrCnt90d,publishDays,today)
    try:
        cursor.execute(sql)
    except Exception as e:
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('阿里巴巴实力优品 %s 信息插入成功！', productId)

# 将阿里巴巴产品分组信息存入到数据库
def insert_product_group(groupId, groupName,hasChildren,childrenCount, productCount,groupLevel,parentGroupId):
    sql = "insert into alibaba_product_group (groupId, groupName,hasChildren,childrenCount, productCount,groupLevel,parentGroupId,create_time) " \
          "values ('%s','%s','%s','%s','%s','%s','%s',now());" \
          % (groupId, groupName,hasChildren,childrenCount, productCount,groupLevel,parentGroupId)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('%s阿里巴巴产品分组信息插入成功！', groupName)


def update_product_group(groupId,productCount):
    sql = "update alibaba_product_group set productCount='%s' where groupId='%s'" % (groupId,productCount)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('更新阿里巴巴产品分组 %s 数量成功！', groupId)

# 更新产品备注信息
def get_product_remark():
    file = 'F:\\alibaba\json\\groupRemark.txt'
    group_remark = replace_txt(file)
    for key in group_remark:
        #update_product(group_remark[key],key)
        exist_product(key)

def exist_product(id):
    sql = 'select groupRemark from alibaba_product where id=%s' % (id)
    cursor.execute(sql)
    results = cursor.fetchall()
    print(id,results)

def update_product(groupRemark,id):
    sql = "update alibaba_product set groupRemark='%s' where id='%s'" % (groupRemark,id)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('更新阿里巴巴产品备注信息 %s 数量成功！', id)

def get_product():
    file = 'F:\\alibaba\json\\product.txt'
    product_list = replace_txt(file)
    for product in product_list:
        id = product['id']
        groupName1 = product['groupName1']
        groupName2 = product['groupName2']
        showNum = product['showNum']
        clickNum = product['clickNum']
        qualityScore = product['qualityScore']
        tradeType = product['tradeType']
        subject = product['subject']
        keywords=product['keywords']
        insert_product(id,groupName1,groupName2,showNum,clickNum,qualityScore,tradeType,subject,keywords)

# 将阿里巴巴产品分组信息存入到数据库
def insert_product(id,groupName1,groupName2,showNum,clickNum,qualityScore,tradeType,subject,keywords):
    sql = "insert into alibaba_product (id,groupName1,groupName2,showNum,clickNum,qualityScore,tradeType,subject,keywords,create_time) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s',now());" \
          % (id,groupName1,groupName2,showNum,clickNum,qualityScore,tradeType,subject,keywords)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('阿里巴巴产品 %s 信息插入成功！', subject)


if __name__ == '__main__':
    #database()
    #get_product()
    #get_product_remark()
    #get_product_rank()
    #get_product_ad()  # 橱窗产品
    get_product_hq()  # 实力优品